package com.hifar.test.equip.util;

import com.hifar.plat.AopOption.Dict;
import com.hifar.plat.AopOption.Excel;
import com.hifar.plat.AopOption.utils.oConvertUtils;
import com.hifar.plat.maindatasource.service.IMainDataSourceService;
import com.hifar.sys.BusiResponse;
import com.hifar.fw.utils.lang.StringUtils;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayOutputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 自定义Excel导出工具
 * 解决ExcelTemplateUtil注解识别问题
 */
public class CustomExcelExportUtil {
    
    /**
     * 导出Excel数据
     * @param busiResponse 响应对象
     * @param dataList 数据列表
     * @param clazz 数据类型
     * @param fileName 文件名
     * @param sheetName 工作表名
     */
    public static <T> void exportExcel(BusiResponse busiResponse, List<T> dataList, Class<T> clazz, String fileName, String sheetName) {
        exportExcel(busiResponse, dataList, clazz, fileName, sheetName, null);
    }
    
    /**
     * 导出Excel数据（支持字典项解析）
     * @param busiResponse 响应对象
     * @param dataList 数据列表
     * @param clazz 数据类型
     * @param fileName 文件名
     * @param sheetName 工作表名
     * @param mainDataSourceService 数据源服务（用于字典项解析）
     */
    public static <T> void exportExcel(BusiResponse busiResponse, List<T> dataList, Class<T> clazz, String fileName, String sheetName, IMainDataSourceService mainDataSourceService) {
        System.out.println("=== 使用CustomExcelExportUtil导出Excel ===");
        System.out.println("数据类型: " + clazz.getName());
        System.out.println("数据条数: " + (dataList != null ? dataList.size() : 0));
        
        Workbook workbook = null;
        try {
            // 创建工作簿
            workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet(sheetName);
            
            // 获取带Excel注解的字段
            List<FieldInfo> fieldInfoList = getExcelFields(clazz);
            
            if (fieldInfoList.isEmpty()) {
                throw new RuntimeException("没有找到任何Excel注解字段");
            }
            
            // 按sort排序
            fieldInfoList.sort(Comparator.comparingInt(f -> f.sort));
            
            // 创建标题行
            Row headerRow = sheet.createRow(0);
            CellStyle headerStyle = workbook.createCellStyle();
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerStyle.setFont(headerFont);
            headerStyle.setAlignment(HorizontalAlignment.CENTER);
            headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            
            for (int i = 0; i < fieldInfoList.size(); i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(fieldInfoList.get(i).name);
                cell.setCellStyle(headerStyle);
            }
            
            // 获取字典项映射（如果提供了数据源服务）
            Map<String, String> dictMapping = new HashMap<>();
            if (mainDataSourceService != null) {
                try {
                    // 第一步：查询字典分类表获取ID
                    String classifySql = "select id, dict_code from base_sys_dict_classify where " +
                            "dict_code in('hf_res_equip_use','equip_classify_type','hf_res_equip_type','hf_res_equip_status') and status<>99 and del_flag=0";
                    List<Map<String, Object>> classifyList = mainDataSourceService.selectList(classifySql);
                    
                    if (!classifyList.isEmpty()) {
                        // 构建分类ID列表
                        String classifyIds = classifyList.stream()
                                .map(item -> StringUtils.trimNull(item.get("id")))
                                .collect(Collectors.joining(","));
                        
                        // 第二步：根据分类ID查询字典项
                        String itemSql = "select c.dict_code, i.item_key, i.item_value from base_sys_dict_item i " +
                                "inner join base_sys_dict_classify c on i.dict_id = c.id " +
                                "where i.dict_id in(" + classifyIds + ") and i.status<>99 and i.del_flag=0";
                        List<Map<String, Object>> dictList = mainDataSourceService.selectList(itemSql);
                        
                        dictMapping = dictList.stream().collect(Collectors.toMap(
                                item -> StringUtils.trimNull(item.get("dictCode")) + "#" + StringUtils.trimNull(item.get("itemKey")),
                                item -> StringUtils.trimNull(item.get("itemValue")), 
                                (o1, o2) -> o1));
                    }
                } catch (Exception e) {
                    System.out.println("导出-获取字典项失败: " + e.getMessage());
                    e.printStackTrace();
                }
            }
            
            // 设置下拉框（数据验证）
            setupDataValidation(sheet, fieldInfoList, dictMapping);
            
            // 填充数据
            if (dataList != null && !dataList.isEmpty()) {
                for (int rowIndex = 0; rowIndex < dataList.size(); rowIndex++) {
                    Row dataRow = sheet.createRow(rowIndex + 1);
                    T data = dataList.get(rowIndex);
                    
                    // 处理字典项转换
                    if (mainDataSourceService != null) {
                        processDictFields(data, dictMapping);
                    }
                    
                    for (int colIndex = 0; colIndex < fieldInfoList.size(); colIndex++) {
                        FieldInfo fieldInfo = fieldInfoList.get(colIndex);
                        Cell cell = dataRow.createCell(colIndex);
                        
                        try {
                            fieldInfo.field.setAccessible(true);
                            Object value = fieldInfo.field.get(data);
                            setCellValue(cell, value, fieldInfo);
                        } catch (IllegalAccessException e) {
                            cell.setCellValue("");
                        }
                    }
                }
            }
            
            // 优化列宽度设置
            optimizeColumnWidths(sheet, fieldInfoList, dataList);
            
            // 输出到字节数组
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);
            
            // 设置响应
            busiResponse.setCode(200);
            busiResponse.setData(outputStream.toByteArray());
            
            System.out.println("✓ Excel导出成功，数据大小: " + outputStream.size() + " 字节");
            
        } catch (Exception e) {
            System.out.println("✗ Excel导出失败: " + e.getMessage());
            e.printStackTrace();
            busiResponse.setCode(500);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
    /**
     * 导出Excel模板
     * @param busiResponse 响应对象
     * @param clazz 数据类型
     * @param fileName 文件名
     * @param sheetName 工作表名
     */
    public static <T> void exportExcelTemplate(BusiResponse busiResponse, Class<T> clazz, String fileName, String sheetName) {
        exportExcelTemplate(busiResponse, clazz, fileName, sheetName, null);
    }
    
    /**
     * 导出Excel模板（支持字典项和下拉框）
     * @param busiResponse 响应对象
     * @param clazz 数据类型
     * @param fileName 文件名
     * @param sheetName 工作表名
     * @param mainDataSourceService 数据源服务（用于字典项解析和下拉框设置）
     */
    public static <T> void exportExcelTemplate(BusiResponse busiResponse, Class<T> clazz, String fileName, String sheetName, IMainDataSourceService mainDataSourceService) {
        System.out.println("=== 使用CustomExcelExportUtil导出Excel模板 ===");
        System.out.println("数据类型: " + clazz.getName());
        exportExcel(busiResponse, new ArrayList<>(), clazz, fileName, sheetName, mainDataSourceService);
    }
    
    /**
     * 获取带Excel注解的字段信息
     */
    private static <T> List<FieldInfo> getExcelFields(Class<T> clazz) {
        List<FieldInfo> fieldInfoList = new ArrayList<>();
        Field[] fields = clazz.getDeclaredFields();
        
        System.out.println("=== CustomExcelExportUtil 注解读取 ===");
        System.out.println("类名: " + clazz.getName());
        System.out.println("字段总数: " + fields.length);
        
        for (Field field : fields) {
            Excel excelAnnotation = field.getAnnotation(Excel.class);
            if (excelAnnotation != null) {
                FieldInfo fieldInfo = new FieldInfo();
                fieldInfo.field = field;
                fieldInfo.name = excelAnnotation.name();
                fieldInfo.sort = excelAnnotation.sort();
                fieldInfo.format = excelAnnotation.format();
                fieldInfo.width = excelAnnotation.width();
                fieldInfo.valueMapping = excelAnnotation.valueMapping();
                fieldInfo.dictCode = excelAnnotation.dictCode();
                
                fieldInfoList.add(fieldInfo);
                System.out.println("✓ 找到Excel字段: " + field.getName() + 
                    " | 列名: " + fieldInfo.name + 
                    " | sort: " + fieldInfo.sort);
            } else {
                System.out.println("✗ 字段无Excel注解: " + field.getName());
            }
        }
        
        System.out.println("Excel字段总数: " + fieldInfoList.size());
        System.out.println("==============================");
        
        return fieldInfoList;
    }
    
    /**
     * 处理字典项字段转换
     */
    private static <T> void processDictFields(T data, Map<String, String> dictMapping) {
        try {
            for (Field field : oConvertUtils.getAllFields(data)) {
                if (field.getAnnotation(Dict.class) != null) {
                    String code = field.getAnnotation(Dict.class).dictCode();
                    field.setAccessible(true);
                    try {
                        String key = BeanUtils.getProperty(data, field.getName());
                        if (key != null) {
                            String value = dictMapping.get(code + "#" + key);
                            if (value != null) {
                                BeanUtils.setProperty(data, field.getName(), value);
                            }
                        }
                    } catch (Exception e) {
                        System.out.println("处理字典项失败: " + field.getName() + " - " + e.getMessage());
                    }
                }
            }
        } catch (Exception e) {
            System.out.println("处理字典项字段失败: " + e.getMessage());
        }
    }
    
    /**
     * 设置单元格值
     */
    private static void setCellValue(Cell cell, Object value, FieldInfo fieldInfo) {
        if (value == null) {
            cell.setCellValue("");
            return;
        }
        
        // 应用值映射转换（反向映射）
        String cellValue = applyValueMappingForExport(value.toString(), fieldInfo);
        
        if (value instanceof Date && !fieldInfo.format.isEmpty()) {
            try {
                SimpleDateFormat dateFormat = new SimpleDateFormat(fieldInfo.format);
                cell.setCellValue(dateFormat.format((Date) value));
            } catch (Exception e) {
                cell.setCellValue(cellValue);
            }
        } else if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
        } else {
            cell.setCellValue(cellValue);
        }
    }
    
    /**
     * 应用值映射转换（导出时的反向映射）
     * 将数据库值转换为Excel显示值
     */
    private static String applyValueMappingForExport(String value, FieldInfo fieldInfo) {
        if (value == null || value.trim().isEmpty() || fieldInfo.valueMapping == null || fieldInfo.valueMapping.trim().isEmpty()) {
            return value;
        }
        
        try {
            // 解析映射配置：格式为 "源值1:目标值1,源值2:目标值2"
            String[] mappings = fieldInfo.valueMapping.split(",");
            for (String mapping : mappings) {
                String[] parts = mapping.split(":");
                if (parts.length == 2) {
                    String sourceValue = parts[0].trim();
                    String targetValue = parts[1].trim();
                    // 反向映射：如果数据库值是目标值，则显示为源值
                    if (targetValue.equals(value)) {
                        System.out.println("导出值映射: [" + value + "] -> [" + sourceValue + "]");
                        return sourceValue;
                    }
                }
            }
        } catch (Exception e) {
            System.out.println("导出值映射解析失败: " + fieldInfo.valueMapping + ", 错误: " + e.getMessage());
        }
        
        return value; // 未找到映射，返回原值
    }
    
    /**
     * 设置数据验证（下拉框）
     */
    private static void setupDataValidation(Sheet sheet, List<FieldInfo> fieldInfoList, Map<String, String> dictMapping) {
        try {
            for (int i = 0; i < fieldInfoList.size(); i++) {
                FieldInfo fieldInfo = fieldInfoList.get(i);
                
                // 检查是否有dictCode或valueMapping
                if (StringUtils.isNotBlank(fieldInfo.dictCode) || StringUtils.isNotBlank(fieldInfo.valueMapping)) {
                    String[] options = getDropdownOptions(fieldInfo, dictMapping);
                    if (options != null && options.length > 0) {
                        // 创建数据验证
                        DataValidationHelper validationHelper = sheet.getDataValidationHelper();
                        DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(options);
                        
                        // 设置验证范围（从第2行开始，最多1000行）
                        CellRangeAddressList regions = new CellRangeAddressList(1, 1000, i, i);
                        DataValidation validation = validationHelper.createValidation(constraint, regions);
                        
                        // 设置验证属性
                        validation.setShowErrorBox(true);
                        validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                        validation.createErrorBox("输入错误", "请从下拉列表中选择有效值");
                        
                        // 应用验证
                        sheet.addValidationData(validation);
                        
                        System.out.println("为列 " + fieldInfo.name + " 设置下拉框，选项: " + String.join(",", options));
                    }
                }
            }
        } catch (Exception e) {
            System.out.println("设置下拉框失败: " + e.getMessage());
        }
    }
    
    /**
     * 获取下拉框选项
     */
    private static String[] getDropdownOptions(FieldInfo fieldInfo, Map<String, String> dictMapping) {
        List<String> options = new ArrayList<>();
        
        try {
            // 优先处理valueMapping
            if (StringUtils.isNotBlank(fieldInfo.valueMapping)) {
                String[] mappings = fieldInfo.valueMapping.split(",");
                for (String mapping : mappings) {
                    String[] parts = mapping.split(":");
                    if (parts.length == 2) {
                        String sourceValue = parts[0].trim();
                        options.add(sourceValue);
                    }
                }
            }
            // 如果没有valueMapping，处理dictCode
            else if (StringUtils.isNotBlank(fieldInfo.dictCode) && dictMapping != null) {
                // 从字典映射中获取所有选项
                for (Map.Entry<String, String> entry : dictMapping.entrySet()) {
                    if (entry.getKey().startsWith(fieldInfo.dictCode + "#")) {
                        options.add(entry.getValue());
                    }
                }
            }
            
            return options.toArray(new String[0]);
        } catch (Exception e) {
            System.out.println("获取下拉框选项失败: " + e.getMessage());
            return new String[0];
        }
    }
    
    /**
     * 优化列宽度设置
     */
    private static <T> void optimizeColumnWidths(Sheet sheet, List<FieldInfo> fieldInfoList, List<T> dataList) {
        try {
            for (int i = 0; i < fieldInfoList.size(); i++) {
                FieldInfo fieldInfo = fieldInfoList.get(i);
                
                // 计算最优列宽
                int optimalWidth = calculateOptimalColumnWidth(sheet, i, fieldInfo, dataList);
                
                // 设置列宽（最小8，最大50）
                int finalWidth = Math.max(8, Math.min(50, optimalWidth));
                sheet.setColumnWidth(i, finalWidth * 256); // Excel列宽单位是1/256字符
                
                System.out.println("列 " + fieldInfo.name + " 设置宽度: " + finalWidth + " 字符");
            }
        } catch (Exception e) {
            System.out.println("设置列宽失败: " + e.getMessage());
            // 回退到自动调整
            for (int i = 0; i < fieldInfoList.size(); i++) {
                sheet.autoSizeColumn(i);
            }
        }
    }
    
    /**
     * 计算最优列宽
     */
    private static <T> int calculateOptimalColumnWidth(Sheet sheet, int columnIndex, FieldInfo fieldInfo, List<T> dataList) {
        try {
            // 1. 如果注解中指定了宽度，直接使用指定的宽度
            if (fieldInfo.width > 0) {
                System.out.println("使用注解指定宽度: " + fieldInfo.name + " = " + fieldInfo.width + " 字符");
                return fieldInfo.width;
            }
            
            // 2. 如果注解中未指定宽度，则使用智能计算
            int maxWidth = 0;
            
            // 2.1 考虑列标题宽度
            String headerText = fieldInfo.name;
            int headerWidth = calculateTextWidth(headerText);
            maxWidth = Math.max(maxWidth, headerWidth);
            
            // 2.2 考虑数据内容宽度（如果有数据）
            if (dataList != null && !dataList.isEmpty()) {
                // 只检查前10行数据，避免性能问题
                int checkRows = Math.min(10, dataList.size());
                for (int rowIndex = 0; rowIndex < checkRows; rowIndex++) {
                    Row row = sheet.getRow(rowIndex + 1);
                    if (row != null) {
                        Cell cell = row.getCell(columnIndex);
                        if (cell != null) {
                            String cellValue = getCellValueAsString(cell);
                            int cellWidth = calculateTextWidth(cellValue);
                            maxWidth = Math.max(maxWidth, cellWidth);
                        }
                    }
                }
            }
            
            // 2.3 根据字段类型设置默认宽度
            int defaultWidth = getDefaultWidthForFieldType(fieldInfo);
            maxWidth = Math.max(maxWidth, defaultWidth);
            
            // 2.4 考虑下拉框选项宽度
            if (StringUtils.isNotBlank(fieldInfo.dictCode) || StringUtils.isNotBlank(fieldInfo.valueMapping)) {
                // 下拉框字段需要更宽一些
                maxWidth = Math.max(maxWidth, 12);
            }
            
            System.out.println("智能计算宽度: " + fieldInfo.name + " = " + maxWidth + " 字符");
            return maxWidth;
        } catch (Exception e) {
            System.out.println("计算列宽失败: " + e.getMessage());
            return 15; // 默认宽度
        }
    }
    
    /**
     * 计算文本显示宽度（中文字符按2个字符计算）
     */
    private static int calculateTextWidth(String text) {
        if (text == null || text.isEmpty()) {
            return 0;
        }
        
        int width = 0;
        for (char c : text.toCharArray()) {
            if (c >= 0x4E00 && c <= 0x9FFF) {
                // 中文字符
                width += 2;
            } else {
                // 英文字符
                width += 1;
            }
        }
        return width;
    }
    
    /**
     * 获取单元格值作为字符串
     */
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
    
    /**
     * 根据字段类型获取默认宽度
     */
    private static int getDefaultWidthForFieldType(FieldInfo fieldInfo) {
        String fieldName = fieldInfo.name.toLowerCase();
        
        // 根据字段名称判断类型
        if (fieldName.contains("编号") || fieldName.contains("代码")) {
            return 15; // 编号类字段
        } else if (fieldName.contains("日期") || fieldName.contains("时间")) {
            return 12; // 日期类字段
        } else if (fieldName.contains("状态") || fieldName.contains("单位")) {
            return 10; // 状态类字段
        } else if (fieldName.contains("备注") || fieldName.contains("描述")) {
            return 20; // 备注类字段
        } else if (fieldName.contains("名称")) {
            return 18; // 名称类字段
        } else {
            return 12; // 默认宽度
        }
    }
    
    /**
     * 字段信息类
     */
    private static class FieldInfo {
        Field field;
        String name;
        int sort;
        String format;
        int width;
        String valueMapping;
        String dictCode;
    }
}

